------------------------------------------------------------------------------------------------------------------------
/*
The views here contain most of the logic for creating the website content.
The views are grouped in the same order as the menu on the site.
Future Improvements:
1. Be more consistent with excluding refunded and fundraising statuses.
2. Be more consistent with using posted_date or disbursal_date.
3. Convert to ANSI syntax.
4. Convert to more efficient to_string method.
Created by Jon Heller (jonearles@yahoo.com)
*/
------------------------------------------------------------------------------------------------------------------------
--Disable variable substitution. For TOAD, right-click in SQL Editor and uncheck "Prompt for Substitution Variables"
set scan off;
set define off;
--This materialized views throws an ORA-600 when you try to refresh it, so drop and recreate it instead.
drop materialized view mv_metrics;
create materialized view mv_metrics refresh on demand as
--Takes 10 seconds to create
select
region, regionNumber, country, countryNumber, variableType, description, value
from
(
with loanMetrics as
(
select region, max(regionNumber) regionNumber, country, max(countryNumber) countryNumber
,max(numberOfLoans) numberOfLoans,max(defaulted) defaulted,max(defaultedPercent) defaultedPercent
,max(paid) paid,max(paidPercent) paidPercent,max(in_repayment) in_repayment
,max(in_repaymentPercent) in_repaymentPercent,max(funded) funded,max(fundedPercent) fundedPercent
,max(refunded) refunded,max(refundedPercent) refundedPercent,max(fundraising) fundraising
,max(fundraisingPercent) fundraisingPercent,max(amountLoaned) amountLoaned,max(numberOfSectors) numberOfSectors
,max(numberOfActivities) numberOfActivities,max(numberOfCountries) numberOfCountries
,max(numberOfTowns) numberOfTowns,max(numberOfPartners) numberOfPartners,max(numberOfCurrencies) numberOfCurrencies
,max(largestLoan) largestLoan,max(smallestLoan) smallestLoan,max(averageLoan) averageLoan
,max(averageLoanPerBorrower) averageLoanPerBorrower,max(firstDisbursalDate) firstDisbursalDate
,max(firstPostedDate) firstPostedDate,max(firstFundedDate) firstFundedDate
,max(firstPaidDate) firstPaidDate,max(firstRefundedDate) firstRefundedDate,max(numberOfLanguages) numberOfLanguages
,max(numberOfJournalEntries) numberOfJournalEntries,max(averageJournalEntries) averageJournalEntries
,max(numberOfPayments) numberOfPayments,max(averagePayments) averagePayments
,max(numberOfScheduledPayments) numberOfScheduledPayments,max(averageScheduledPayments) averageScheduledPayments
,max(numberOfLocalPayments) numberOfLocalPayments,max(averageLocalPayments) averageLocalPayments
,max(numberOfBorrowers) numberOfBorrowers,max(averageBorrowers) averageBorrowers
from
(
select countries.region, countries.regionNumber, countries.country, countries.countryNumber
,to_char(count(*)) numberOfLoans
,to_char(count(case when status = 'defaulted' then 1 else null end)) defaulted
,trim(to_char(count(case when status = 'defaulted' then 1 else null end) / count(*) * 100, '990.00')) defaultedPercent
,to_char(count(case when status = 'paid' then 1 else null end)) paid
,trim(to_char(count(case when status = 'paid' then 1 else null end) / count(*) * 100, '990.00')) paidPercent
,to_char(count(case when status = 'in_repayment' then 1 else null end)) in_repayment
,trim(to_char(count(case when status = 'in_repayment' then 1 else null end) / count(*) * 100, '990.00')) in_repaymentPercent
,to_char(count(case when status = 'funded' then 1 else null end)) funded
,trim(to_char(count(case when status = 'funded' then 1 else null end) / count(*) * 100, '990.00')) fundedPercent
,to_char(count(case when status = 'refunded' then 1 else null end)) refunded
,trim(to_char(count(case when status = 'refunded' then 1 else null end) / count(*) * 100, '990.00')) refundedPercent
,to_char(count(case when status = 'fundraising' then 1 else null end)) fundraising
,trim(to_char(count(case when status = 'fundraising' then 1 else null end) / count(*) * 100, '990.00')) fundraisingPercent
,to_char(sum(loan_amount)) amountLoaned
,to_char(count(distinct sector)) numberOfSectors
,to_char(count(distinct activity)) numberOfActivities
,to_char(count(distinct loan.country)) numberOfCountries
,to_char(count(distinct town)) numberOfTowns
,to_char(count(distinct partner_id)) numberOfPartners
,to_char(count(distinct disbursal_currency)) numberOfCurrencies
,to_char(max(loan_amount)) largestLoan
,to_char(min(loan_amount)) smallestLoan
,trim(to_char(avg(loan_amount),'999990.0')) averageLoan
,trim(to_char(avg(loan_amount / borrowers.total),'999990.0')) averageLoanPerBorrower
,to_char(min(disbursal_date), 'DD Mon YYYY') firstDisbursalDate
,to_char(min(posted_date), 'DD Mon YYYY') firstPostedDate
,to_char(min(funded_date), 'DD Mon YYYY') firstFundedDate
,to_char(min(paid_date), 'DD Mon YYYY') firstPaidDate
,to_char(min(refunded_date), 'DD Mon YYYY') firstRefundedDate
,null numberOfLanguages
,to_char(count(journal_entries)) numberOfJournalEntries
,trim(to_char(nvl(avg(journal_entries), 0), '999,990.00')) averageJournalEntries
,to_char(nvl(sum(payments.total), 0)) numberOfPayments
,trim(to_char(nvl(avg(payments.total), 0), '999,990.00')) averagePayments
,to_char(nvl(sum(scheduledPayments.total), 0)) numberOfScheduledPayments
,trim(to_char(nvl(avg(scheduledPayments.total), 0), '999,990.00')) averageScheduledPayments
,to_char(nvl(sum(localPayments.total), 0)) numberOfLocalPayments
,trim(to_char(nvl(avg(localPayments.total), 0), '999,990.00')) averageLocalPayments
,to_char(nvl(sum(borrowers.total), 0)) numberOfBorrowers
,trim(to_char(nvl(avg(borrowers.total), 0), '999,990.00')) averageBorrowers
from loan
,(select distinct country, country.region, dense_rank()over(order by country)-1 countryNumber
,dense_rank()over(order by country.region) regionNumber
from loan, country
where loan.country = country.name) countries
,(select loan_id, count(*) total from payment group by loan_id) payments
,(select loan_id, count(*) total from scheduledpayment group by loan_id) scheduledPayments
,(select loan_id, count(*) total from localPayment group by loan_id) localPayments
,(select loan_id, count(*) total from borrower group by loan_id) borrowers
where loan.country = countries.country
and loan.id = payments.loan_id (+)
and loan.id = scheduledpayments.loan_id (+)
and loan.id = localPayments.loan_id (+)
and loan.id = borrowers.loan_id (+)
group by grouping sets ( ()
,(countries.region, countries.regionNumber)
,(countries.country, countries.countryNumber)
)
---------
union all
---------
--Number of languages must be calculated separately, since it requires a distinct count for each
--grouping set, and there's no way to do this and combine it with the other sums (without duplicating
--the rows for the other metrics). (TODO: Is there a better way to do this?)
select
country.region, null regionNumber, loan.country, null countryNumber
,null numberOfLoans,null defaulted,null defaultedPercent,null paid,null paidPercent,null in_repayment
,null in_repaymentPercent,null funded,null fundedPercent,null refunded,null refundedPercent,null fundraising
,null fundraisingPercent,null amountLoaned,null numberOfSectors,null numberOfActivities,null numberOfCountries
,null numberOfTowns,null numberOfPartners,null numberOfCurrencies,null largestLoan,null smallestLoan
,null averageLoan,null averageLoanPerBorrower,null firstDisbursalDate,null firstPostedDate,null firstFundedDate
,null firstPaidDate,null firstRefundedDate
,to_char(nvl(count(distinct loan_language.code), 0)) numberOfLanguages
,null numberOfJournalEntries,null averageJournalEntries,null numberOfPayments,null averagePayments
,null numberOfScheduledPayments,null averageScheduledPayments,null numberOfLocalPayments
,null averageLocalPayments,null numberOfBorrowers,null averageBorrowers
from loan, loan_language, country
where loan.id = loan_language.loan_id
and loan.country = country.name
group by grouping sets ( (country.region), (loan.country), () )
)
group by region, country
)
----------------------------------------------------------------------------------------------------------------------
select 'number' variableType, region, regionNumber, country, countryNumber, 'Loans (#)' description, numberOfLoans value from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Status: defaulted (#)', defaulted from loanMetrics union all
select 'percent' variableType, region, regionNumber, country, countryNumber, 'Status: defaulted (%)', defaultedPercent from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Status: funded (#)', funded from loanMetrics union all
select 'percent' variableType, region, regionNumber, country, countryNumber, 'Status: funded (%)', fundedPercent from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Status: fundraising (#)', fundraising from loanMetrics union all
select 'percent' variableType, region, regionNumber, country, countryNumber, 'Status: fundraising (%)', fundraisingPercent from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Status: in_repayment (#)', in_repayment from loanMetrics union all
select 'percent' variableType, region, regionNumber, country, countryNumber, 'Status: in_repayment (%)', in_repaymentPercent from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Status: paid (#)', paid from loanMetrics union all
select 'percent' variableType, region, regionNumber, country, countryNumber, 'Status: paid (%)', paidPercent from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Status: refunded #', refunded from loanMetrics union all
select 'percent' variableType, region, regionNumber, country, countryNumber, 'Status: refunded (%)', refundedPercent from loanMetrics union all
select 'money' variableType, region, regionNumber, country, countryNumber, 'Amount loaned (sum)', amountLoaned from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Sectors (#)', numberOfSectors from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Activities (#)', numberOfActivities from loanMetrics union all
--This doesn't apply to the country metrics
select 'number' variableType, region, regionNumber, country, countryNumber, 'Countries (#)', numberOfCountries from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Towns (#)', numberOfTowns from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Partners (#)', numberOfPartners from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Currencies (#)', numberOfCurrencies from loanMetrics union all
select 'money' variableType, region, regionNumber, country, countryNumber, 'Amount loaned (max)', largestLoan from loanMetrics union all
select 'money' variableType, region, regionNumber, country, countryNumber, 'Amount loaned (min)', smallestLoan from loanMetrics union all
select 'money' variableType, region, regionNumber, country, countryNumber, 'Amount loaned (avg)', averageLoan from loanMetrics union all
select 'money' variableType, region, regionNumber, country, countryNumber, 'Amt. per borrower (avg)', averageLoanPerBorrower from loanMetrics union all
select 'date' variableType, region, regionNumber, country, countryNumber, 'Date disbursed (first)', firstDisbursalDate from loanMetrics union all
select 'date' variableType, region, regionNumber, country, countryNumber, 'Date posted (first)', firstPostedDate from loanMetrics union all
select 'date' variableType, region, regionNumber, country, countryNumber, 'Date funded (first)', firstFundedDate from loanMetrics union all
select 'date' variableType, region, regionNumber, country, countryNumber, 'Date Paid (first)', firstPaidDate from loanMetrics union all
select 'date' variableType, region, regionNumber, country, countryNumber, 'Date refunded (first)', firstRefundedDate from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Languages (#)', numberOfLanguages from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Journal entries (#)', numberOfJournalEntries from loanMetrics union all
select 'decimal' variableType, region, regionNumber, country, countryNumber, 'Journal entries (avg)', averageJournalEntries from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Payments (#)', numberOfPayments from loanMetrics union all
select 'decimal' variableType, region, regionNumber, country, countryNumber, 'Payments (avg)', averagePayments from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Scheduled payments (#)', numberOfScheduledPayments from loanMetrics union all
select 'decimal' variableType, region, regionNumber, country, countryNumber, 'Scheduled payments (avg)', averageScheduledPayments from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Local payments (#)', numberOfLocalPayments from loanMetrics union all
select 'decimal' variableType, region, regionNumber, country, countryNumber, 'Local payments (avg)', averageLocalPayments from loanMetrics union all
select 'number' variableType, region, regionNumber, country, countryNumber, 'Borrowers (#)', numberOfBorrowers from loanMetrics union all
select 'decimal' variableType, region, regionNumber, country, countryNumber, 'Borrowers (avg)', averageBorrowers from loanMetrics --union all
)
order by description, regionNumber, countryNumber;
--begin
-- dbms_mview.refresh('MV_METRICS');
--end;
--/
------------------------------------------------------------------------------------------------------------------------
--SUMMARY
------------------------------------------------------------------------------------------------------------------------
create or replace view v_summary_metrics as
select '
'||description||'
'||value||'
' html
from
(
select description
,case
when variableType = 'number' then trim(to_char(value, '999,999,990'))
when variableType = 'percent' then trim(to_char(value, '999,999,990.00'))||'%'
when variableType = 'money' then '$'||trim(to_char(value, '999,999,990'))
else value
end value
from mv_metrics
where region is null
and country is null
order by description
);
create or replace view v_summary_cumulativeLoans as
--ALSO USED IN HOME PAGE
select
case when rownumber = 1 then 'data.addRows('||to_char(numberOfRows)||');' else null end ||
--Display all months, let Google Charts API sort out which labels to print
'data.setValue('||to_char(rownumber-1)||', 0, "'||to_char(to_date(yearAndMonth, 'YYYYMM'), 'Mon YYYY')||'");'||
'data.setValue('||to_char(rowNumber-1)||', 1, '||total||');' javascript
from
(
select yearAndMonth, sum(total) over (order by yearAndMonth rows between unbounded preceding and current row) total
,row_number() over (order by yearAndMonth) rownumber, count(total) over () numberOfRows
from
(
select months.yearAndMonth, count(loans.yearAndMonth) total
from
(select yearAndMonth from mv_months) months
,(select to_char(disbursal_date, 'YYYYMM') yearAndMonth from loan
where disbursal_date is not null) loans --two loans don't have a date, and will throw off results
where months.yearAndMonth = loans.yearAndMonth (+)
group by months.yearAndMonth
order by months.yearAndMonth
)
);
/* TOO MUCH DATA, DOESN'T WORK
--Cumulative Loans Timeline
--Use date from first row
select case when row_number() over (order by hours.datetime) <> 1 then ',' else null end||
sum(nvl(loans.total, 0)) over (order by hours.datetime)
,case when row_number() over (order by hours.datetime) <> 1 then null else
'('||to_char(to_date(hours.datetime, 'YYYYMMDDHH24'), 'YYYY,MM,DD,HH24')||',0,0)'
end firstTime
--Old method that used separate statements for each value
--case when row_number() over (order by hours.datetime) <> 1 then ',' else null end||
--'{c:[{v:new Date('||to_char(to_date(hours.datetime, 'YYYYMMDDHH24'), 'YYYY,MM,DD,HH24')||',0,0)},{v:'||
-- sum(nvl(loans.total, 0)) over (order by hours.datetime)||'}]}' javascript
from
(
--All hours since the beginning, including first and last
select to_char((select min(posted_date) from loan) + (level - 1)/24, 'YYYYMMDDHH24') datetime
from dual
connect by level <= ((select max(posted_date) from loan) - ( select min(posted_date) from loan)) * 24 + 1/24
) hours
left outer join
(
select to_char(posted_date, 'YYYYMMDDHH24') datetime, count(id) total
from loan
group by to_char(posted_date, 'YYYYMMDDHH24')
) loans
on hours.datetime = loans.datetime
order by hours.datetime;
*/
/* OLD GOOGLE CHARTS IMAGE
--TODO: Add some useful tickmarks for months
--Cumulative loans
with loansPerMonth as
(
select yearAndMonth, sum(total) over (order by yearAndMonth rows between unbounded preceding and current row) total
from
(
select months.yearAndMonth, count(loans.yearAndMonth) total
from
(
--Exclude the current month, since it will contain partial data
select to_char(add_months(sysdate, -(level)), 'YYYYMM') yearAndMonth
from dual
connect by level <= months_between(sysdate, (select min(disbursal_date) from loan))+1
) months
,(select to_char(disbursal_date, 'YYYYMM') yearAndMonth from loan
where disbursal_date is not null) loans --two loans don't have a date, and will throw off results
where months.yearAndMonth = loans.yearAndMonth (+)
group by months.yearAndMonth
-- order by months.yearAndMonth
)
),
totals as
(
select max(total) total, min(yearAndMonth) firstDate, max(yearAndMonth) lastDate
from loansPerMonth
)
------------------------------------------------------------------------------------------------------------------------
select ''
from
(
select f_list(cursor(select total from loansPerMonth), ',') data
,total, to_char(to_date(firstDate, 'YYYYMM'), 'YYYY Mon') firstDate
,to_char(to_date(lastDate, 'YYYYMM'), 'YYYY Mon') lastDate
from totals
);
*/
------------------------------------------------------------------------------------------------------------------------
--REGION
------------------------------------------------------------------------------------------------------------------------
create or replace view v_region_loansPerRegion_good as
select '' html
from
(
select
max(case when region = 'Africa' then data else null end) AfricaData
,max(case when region = 'Asia' then data else null end) AsiaData
,max(case when region = 'Central America' then data else null end) CentralAmericaData
,max(case when region = 'Eastern Europe' then data else null end) EasternEuropeData
,max(case when region = 'Middle East' then data else null end) MiddleEastData
,max(case when region = 'North America' then data else null end) NorthAmericaData
,max(case when region = 'South America' then data else null end) SouthAmericaData
--Display year for first quarter, else just display Q#
--Original version when there was room for full year numbers.
--,regexp_replace(regexp_replace(max(labels), '[0-9]{4}([2-4])', 'Q\1'), '([0-9]{4})1', '\1 Q1') labels
,regexp_replace(regexp_replace(max(labels), '[0-9]{4}([2-4])', 'Q\1'), '[0-9]{2}([0-9]{2})1', '\1 Q1') labels
from
(
select region, to_string(cast(collect(collect_obj(runningPercent, rownumber)) as collect_ntt), ',') data
,to_string(cast(collect(collect_obj(yearAndQuarter, rownumber)) as collect_ntt), '|') labels
from
(
select region, yearAndQuarter--, total, percent
,trim(to_char(sum(percent) over (partition by yearAndQuarter order by yearAndQuarter, region
rows between unbounded preceding and current row),'990')) runningPercent
,row_number() over (partition by region order by yearAndQuarter) rownumber
from
(
select loansPerQuarter.region, quarters.yearAndQuarter, nvl(loansPerQuarter.total,0) total
,nvl(nvl(total,0) / sum(total) over (partition by quarters.yearAndQuarter) * 100, 0) percent
from
(
--Uncomment "where gooddate = 1" to only display meaningful data
--This is the only difference between the "good" and the "bad" version
select distinct yearAndQuarter from mv_months
where gooddata = 1
) quarters
left outer join
(
select country.region, to_char(posted_date, 'YYYYQ') yearAndQuarter
,count(*) total
from loan, country
where loan.country = country.name
group by country.region, to_char(posted_date, 'YYYYQ')
order by yearAndQuarter, region
) loansPerQuarter
partition by (region)
on quarters.yearAndQuarter = loansPerQuarter.yearAndQuarter
order by quarters.yearAndQuarter, loansPerQuarter.region
)
)
group by region
)
);
create or replace view v_region_loansPerRegion_bad as
select '' html
from
(
select
max(case when region = 'Africa' then data else null end) AfricaData
,max(case when region = 'Asia' then data else null end) AsiaData
,max(case when region = 'Central America' then data else null end) CentralAmericaData
,max(case when region = 'Eastern Europe' then data else null end) EasternEuropeData
,max(case when region = 'Middle East' then data else null end) MiddleEastData
,max(case when region = 'North America' then data else null end) NorthAmericaData
,max(case when region = 'South America' then data else null end) SouthAmericaData
--Display year for first quarter, else just display Q#
,regexp_replace(regexp_replace(max(labels), '[0-9]{4}([2-4])', 'Q\1'), '([0-9]{4})1', '\1 Q1') labels
from
(
select region, to_string(cast(collect(collect_obj(runningPercent, rownumber)) as collect_ntt), ',') data
,to_string(cast(collect(collect_obj(yearAndQuarter, rownumber)) as collect_ntt), '|') labels
from
(
select region, yearAndQuarter--, total, percent
,trim(to_char(sum(percent) over (partition by yearAndQuarter order by yearAndQuarter, region
rows between unbounded preceding and current row),'990')) runningPercent
,row_number() over (partition by region order by yearAndQuarter) rownumber
from
(
select loansPerQuarter.region, quarters.yearAndQuarter, nvl(loansPerQuarter.total,0) total
,nvl(nvl(total,0) / sum(total) over (partition by quarters.yearAndQuarter) * 100, 0) percent
from
(
--Uncomment "where gooddate = 1" to only display meaningful data
--This is the only difference between the "good" and the "bad" version
select distinct yearAndQuarter from mv_months
--where gooddata = 1
) quarters
left outer join
(
select country.region, to_char(posted_date, 'YYYYQ') yearAndQuarter
,count(*) total
from loan, country
where loan.country = country.name
group by country.region, to_char(posted_date, 'YYYYQ')
order by yearAndQuarter, region
) loansPerQuarter
partition by (region)
on quarters.yearAndQuarter = loansPerQuarter.yearAndQuarter
order by quarters.yearAndQuarter, loansPerQuarter.region
)
)
group by region
)
);
create or replace view v_region_table as
select
case when region = 'Africa' then '
'||description||'
' else null end ||
'
'||value||'
' ||
case when region = 'South America' then '
' else null end html
from
(
select dense_rank() over (order by description) metricNumber
,variableType, region, description
,case
when variableType = 'number' then trim(to_char(value, '999,999,990'))
when variableType = 'percent' then trim(to_char(value, '999,999,990.00'))||'%'
when variableType = 'money' then '$'||trim(to_char(value, '999,999,990'))
else value
end value
from
(
select * from mv_metrics where region is not null order by description, region
)
);
create or replace view v_region_listOfCountries as
select '
'||region||'
'||countries||'
' html
from
(
select region, to_string(cast(collect(collect_obj(country, rownumber)) as collect_ntt), ', ') countries
from
(
select region, country, row_number() over (partition by region order by country) rownumber
from
(
select distinct region, loan.country
from loan, country
where loan.country = country.name
)
)
group by region
);
------------------------------------------------------------------------------------------------------------------------
--COUNTRY
------------------------------------------------------------------------------------------------------------------------
create or replace view v_country_mapListOfCountries as
select case when rowNumber = 0 then
'd.addRows('||total||');'||
'd.addColumn(''string'',''Country'');'
else null end ||
'd.setValue('||rowNumber||', 0, '''||replace(country, '''', '''''')||''');' javascript
from
(
--Subtract 1 to get 0-based index
select row_number() over (order by country) - 1 rowNumber
--Google maps requires different names for some countries. Escape all quotation marks.
,replace(
replace(
replace(
replace(country, 'Cote D''Ivoire', 'Ivory Coast')
, 'Viet Nam', 'Vietnam')
, 'The Democratic Republic of the Congo', 'CD') --TODO: There has to be a better name than this
, '''', '''''') country
, count(*)over() total from
(
select distinct country from loan
)
order by rownumber
);
create or replace view v_country_map as
--Metrics per Country Map and Table
select
--Create javascript case for the first value for the metric
case when countryNumber = firstCountryNumber then
'case '''||description||''':'||
' d.addColumn('''||case when variableType in ('percent','money','number','decimal') then 'number'
when variableType in ('date') then 'string' else 'ERROR'
end||
''','''||description||''');'
else null end ||
--Add value
case when variableType in ('number', 'decimal', 'money', 'percent') then
'd.setValue('||countryNumber||',1,'||value||');'
when variableType = 'date' then
'd.setValue('||countryNumber||',1,"'||value||'");'
else 'ERROR' end ||
--Add formatter for last value for the metric
case when countrynumber = lastCountryNumber then
case when variableType = 'number' then
' var f = new google.visualization.TableNumberFormat({groupingSymbol:",",fractionDigits:0}); '||
' f.format(d,1); break;'
when variableType = 'decimal' then
' var f = new google.visualization.TableNumberFormat({groupingSymbol:",",fractionDigits:2}); '||
' f.format(d, 1); break;'
when variableType = 'money' then
' var f = new google.visualization.TableNumberFormat({groupingSymbol:",",prefix:"$",fractionDigits:0}); '||
' f.format(d,1); break;'
when variableType = 'percent' then
' var f = new google.visualization.TableNumberFormat({suffix:"%",fractionDigits:2}); '||
' f.format(d,1); break;'
when variableType = 'date' then
' break;' --don't format string
end
else null
end javascript
from
(
select country, countrynumber, variabletype, description, value
,dense_rank() over (order by description) metricNumber
,min(countryNumber) over () firstCountryNumber
,max(countryNumber) over() lastCountrynumber
from mv_metrics
where country is not null
and description <> 'Countries (#)'
order by description, countryNumber
);
create or replace view v_country_mapOptions as
select
'' html
from
(
select country, countrynumber, variabletype, description, value
,dense_rank() over (order by description) metricNumber
,min(countryNumber) over () firstCountryNumber
,max(countryNumber) over() lastCountrynumber
from mv_metrics
where country is not null
and description <> 'Countries (#)'
order by description, countryNumber
)
where countryNumber = firstCountrynumber;
create or replace view v_country_table as
select
--Don't put a comma in front of the first row
case when countryNumber <> 0 then ',' else null end||
'{c:[{v:"'||country||'"},'||
to_string(cast(collect(collect_obj(
case
when variabletype = 'date'
then '{v:new Date('||to_char(to_date(value,'DD Mon YYYY'),'YYYY,MM,DD')||'),f:"'||value||'"}'
else '{v:'||value||'}'
end
,metricNumber)) as collect_ntt)) || ',{v:"'||country||'"}]}' javascript
from
(
select region, regionnumber, country, countrynumber, variabletype, description, value
,dense_rank() over (order by description) metricNumber
from mv_metrics where country is not null and description <> 'Countries (#)'
)
group by countryNumber, country;
create or replace view v_country_formatAndColumnDef as
select
to_string(cast(collect(collect_obj(
--Only need to format number, decimal, and percent
case
when variableType = 'number' then
'var f = new google.visualization.TableNumberFormat({fractionDigits: 0}); '||
'f.format(data, '||(metricNumber)||');'
when variableType = 'decimal' then
'var f = new google.visualization.TableNumberFormat({fractionDigits: 2}); '||
'f.format(data, '||(metricNumber)||');'
when variableType = 'percent' then
'var f = new google.visualization.TableNumberFormat({suffix: "%"}); '||
'f.format(data, '||(metricNumber)||');'
when variableType = 'money' then
'var f = new google.visualization.TableNumberFormat({prefix:"$"}); '||
'f.format(data, '||(metricNumber)||');'
else null
end
,metricNumber)) as collect_ntt), ' ') formatting
,'{id:"Country",label:"Country",type:"string"},'||
to_string(cast(collect(collect_obj(
--Can't use distinct here, instead only return data once per metric (for country 0), collect will exclude nulls
case when countryNumber = 0 then
--Google types are string, number, and date. My types are number, percent, decimal, money, and date.
'{id:"'||description||'",label:"'||description||'",type:"'||decode(variableType,'date','date','number')||'"}'
else null end
,case when countryNumber = 0 then metricNumber else null end)) as collect_ntt)) ||
--And end with country (repeat the column for readability)
',{id:"Country",label:"Country",type:"string"}' columnDefinitions
from
(
select region, regionnumber, country, countrynumber, variabletype, description, value
,dense_rank() over (order by description) metricNumber
from mv_metrics where country is not null and description <> 'Countries (#)'
)
--Only need one row
where countryNumber = 0;
create or replace view v_country_loansPerCountry as
select '' html
from
(
select
to_string(cast(collect(collect_obj(country, rownumber)) as collect_ntt), '|') labels
,to_string(cast(collect(collect_obj(trim(to_char(percent, '990')), rownumber)) as collect_ntt), ',') data
from
(
select
case when rownumber >= 20 then 'Others ('||totalRemaining||')' else country||' ('||total||')' end country
,case when rownumber >= 20 then percentRemaining else percent end percent
,rownumber
from
(
select country, total, rownumber, percent
,sum(total) over (order by rownumber
rows between current row and unbounded following) totalRemaining
,100 - sum(percent) over (order by rownumber
rows between unbounded preceding and current row) percentRemaining
from
(
--Dominican Republic is too large for chart.
select decode(country, 'Dominican Republic', 'Dom. Republic', country) country
,total
,row_number() over (order by total desc, country) rownumber
,total / sum(total) over () * 100 percent
from
(
select country, count(*) total
from loan
group by country
order by total desc
)
--order by rownumber --this crashes Oracle - ORA-07445
)
)
where rownumber <= 20
)
);
--List of all countries
create or replace view v_country_listOfCountries as
select f_list(cursor(select distinct country from loan order by country), ', ') html from dual;
create or replace view v_country_animated_gif as
--MANUAL:
--1. Copy all links into a HTML file, open HTML file in browser, and save images to directory using the number and
-- month as part of the name.
--2. In Inkscape: Open the PNG, add Arial size 18 text at Y 20, centered on the page. If the month has a descender
-- character, such as "y" or "g", then add another line of text without a descender, and align the text to the top
-- of the new text. (This ensures that all text are at the same height, since Inkscape measures distance from the
-- bottom.) Save the files as PNG.
--3. Select all the new files and drag them into GIMP. Filters-->Animation-->Optimize (for GIF). In each layer,
-- change the time from 100 ms to 1000 ms.
--4. File-->Save As, save the file with a GIF extension, select save as animation and then export.
--5. Old images are in /Adhoc results/Animated GIF/. Put the new image in /website/images
--Create cumulative list of countries per month, only displaying months with new countries.
select trim(to_char(rownum, '00'))||': '||to_char(to_date(yearAndMonth, 'YYYYMM'), 'Mon YYYY')||
''||
'
'
html
from
(
--Add a list of "100," for each country.
select yearAndMonth, countries, substr(rpad(' ', 4 * length(countries)/2 + 1, ',100'), 3) numbers
from
(
--Make the list distinct
select yearAndMonth, countries
,case when countries = lag(countries) over (order by yearAndMonth) then 1 else 0 end isDuplicate
from
(
--Create list of countries per month
select yearAndMonth, to_string(cast(collect(collect_obj(iso_code, rownumber)) as collect_ntt), null) countries
from
( --Add rownumber for ordering
select yearAndMonth, iso_code, row_number() over (partition by yearAndMonth order by iso_code) rownumber
from
( --Countries that had a loan on or before each month
select distinct mv_months.yearAndMonth, loans.iso_code
from
mv_months
,(select country.iso_code, least(nvl(disbursal_date, to_date('01-JAN-9999', 'DD-MON-YYYY')),
nvl(posted_date, to_date('01-JAN-9999', 'DD-MON-YYYY')),
nvl(funded_date, to_date('01-JAN-9999', 'DD-MON-YYYY'))) firstDate
from loan, country
where loan.country = country.name
and iso_code is not null --map must have an iso code
--and rownum < 10000 --For testing (it's much faster with less values)
) loans
where to_char(loans.firstDate, 'YYYYMM') <= mv_months.yearAndMonth
order by yearAndMonth
)
)
group by yearAndMonth
)
)
where isDuplicate = 0
)
order by yearAndMonth;
------------------------------------------------------------------------------------------------------------------------
--TOWN
------------------------------------------------------------------------------------------------------------------------
create or replace view v_town_regionMarkers as
select
--I'd like to use multiple lines, but SQL Developer adds quotation marks to multi-line copies, so it makes it
--very inconvenient.
'var region'||rownum||'Point = new GLatLng('||latitude||', '||longitude||');'||
'var region'||rownum||'Marker = new GMarker(region'||rownum||'Point, regionIconMarkerOptions);'||
'GEvent.addListener(region'||rownum||'Marker, "click", function(){'||
' map.openInfoWindowHtml(region'||rownum||'Point, "
'||name||'
# Loans = '||totalLoans||
' Total Loan Amount = '||loanAmount||'");});'||
'regionMarkers.push(region'||rownum||'Marker);' javascript
from
(
select region.name, region.latitude, region.longitude
,trim(to_char(count(loan.id),'999,990')) totalLoans
,'$'||trim(to_char(sum(loan_amount),'999,999,990')) loanAmount
from loan, country, region
where loan.status not in ('refunded', 'fundraising')
and loan.country = country.name
and country.region = region.name
group by region.name, region.latitude, region.longitude
);
create or replace view v_town_regionMarkers as
select
--I'd like to use multiple lines, but SQL Developer adds quotation marks to multi-line copies, so it makes it
--very inconvenient.
'var region'||rownum||'Point = new GLatLng('||latitude||', '||longitude||');'||
'var region'||rownum||'Marker = new GMarker(region'||rownum||'Point, regionIconMarkerOptions);'||
'GEvent.addListener(region'||rownum||'Marker, "click", function(){'||
' map.openInfoWindowHtml(region'||rownum||'Point, "
'||name||'
# Loans = '||totalLoans||
' Total Loan Amount = '||loanAmount||'");});'||
'regionMarkers.push(region'||rownum||'Marker);' javascript
from
(
select region.name, region.latitude, region.longitude
,trim(to_char(count(loan.id),'999,990')) totalLoans
,'$'||trim(to_char(sum(loan_amount),'999,999,990')) loanAmount
from loan, country, region
where loan.status not in ('refunded', 'fundraising')
and loan.country = country.name
and country.region = region.name
group by region.name, region.latitude, region.longitude
);
create or replace view v_town_countryMarkers as
select
--I'd like to use multiple lines, but SQL Developer adds quotation marks to multi-line copies, so it makes it
--very inconvenient.
'var country'||rownum||'Point = new GLatLng('||latitude||', '||longitude||');'||
'var country'||rownum||'Marker = new GMarker(country'||rownum||'Point, countryIconMarkerOptions);'||
'GEvent.addListener(country'||rownum||'Marker, "click", function(){'||
' map.openInfoWindowHtml(country'||rownum||'Point, "
'||name||'
# Loans = '||totalLoans||
' Total Loan Amount = '||loanAmount||'");});'||
'countryMarkers.push(country'||rownum||'Marker);' javascript
from
(
select country.name, country.latitude, country.longitude
,trim(to_char(count(loan.id),'999,990')) totalLoans
,'$'||trim(to_char(sum(loan_amount),'999,999,990')) loanAmount
from loan, country
where loan.status not in ('refunded', 'fundraising')
and loan.country = country.name
group by country.name, country.latitude, country.longitude
);
create or replace view v_town_townMarkers as
select
--Put comma between values, but not the first
case when rownum <> 1 then ',' else null end||
'{a:'||latitude||',o:'||longitude||',h:"
'||town||'
# Loans = '||totalLoans||' Total Loan Amount = '||
loanAmount||'",s:'||
case
when totalLoansNumber >= 101 then 1
when totalLoansNumber between 6 and 100 then 2
when totalLoansNumber between 0 and 5 then 3
end ||'}' javascript
from
(
select town, substr(geopairs, 1, instr(geopairs, ' ')-1) latitude, substr(geopairs, instr(geopairs, ' ')+1) longitude
,trim(to_char(count(loan.id),'999,990')) totalLoans
,count(loan.id) totalLoansNumber
,'$'||trim(to_char(sum(loan_amount),'999,999,990')) loanAmount
from loan
where loan.status not in ('refunded', 'fundraising')
and geolevel = 'town'
group by town, substr(geopairs, 1, instr(geopairs, ' ')-1), substr(geopairs, instr(geopairs, ' ')+1)
);
create or replace view v_town_comment as
select '
There are '||(select trim(to_char(count(*),'999,990')) from (select distinct country, town from loan))
||' distinct towns, but there are '||(select trim(to_char(count(*),'999,990')) from loan where town is null)||
' loans without a town listed so the real number of towns could be much higher.
' html
from dual;
create or replace view v_town_top10Towns as
select '
'||town||'
'||country||'
'||total||'
' html
from
(
select nvl(town, '[none]') town, country, total
from
(
select town, country, count(*) total
from loan
group by town, country
order by count(*) desc
)
where rownum <= 10
order by total desc
);
------------------------------------------------------------------------------------------------------------------------
--STATUS
------------------------------------------------------------------------------------------------------------------------
create or replace view v_status_pieChart as
select '' html
from
(
select
to_string(cast(collect(collect_obj(percent, rownumber)) as collect_ntt), ',') data
,to_string(cast(collect(collect_obj(status, rownumber)) as collect_ntt), '|') labels
from
(
select status||' ('||trim(to_char(total,'999,990'))||')' status
,trim(to_char(total / sum(total) over () * 100,'990')) percent
,total, row_number() over (order by total desc) rownumber
from
(
select status, count(*) total
from loan
group by status
order by total desc
)
)
);
create or replace view v_status_comment as
select 'The status data is not as interesting as I had hoped. I wanted to analyze the data to '||chr(10)||
'determine what makes a loan default, but it seems that loan failure is much more about the partner '||chr(10)||
'than the individual loan. Currently there are '||numberOfPartners||' partners with loans, and only '||
numberOfPartnersWithDefaults||chr(10)||' of them have any defaulted loans. Of those '||numberOfPartnersWithDefaults||
' partners, just '||rowNumberToUse||' of them account for '||runningTotalPercent||' of the defaults.' text
from
(
select trim(to_char(runningTotalPercent, '99.0'))||'%' runningTotalPercent, numberOfPartners
,rowNumberToUse, numberOfPartnersWithDefaults
from
(
select partner_id, defaulted, sum(percent) over (order by defaulted desc
rows between unbounded preceding and current row) runningTotalPercent
,numberOfPartners, numberOfPartnersWithDefaults, rownumber, rowNumberToUse
from
(
select partner_id, defaulted, defaulted/sum(defaulted)over()*100 percent
,count(partner_id) over () numberOfPartners
,count(case when defaulted > 0 then partner_id else null end) over () numberOfPartnersWithDefaults
,row_number() over (order by defaulted desc) rownumber
--This number was manually selected
,4 rowNumberToUse
from
(
select partner_id, count(case when status = 'defaulted' then 1 else null end) defaulted
from loan
group by partner_id
)
order by defaulted desc
)
)
where rownumber = rowNumberToUse
);
--DO NOT USE: Not meaningful data
--Count the number of defaulted loans per country and month.
--The date of failure is the last payment.processed_date, or the last localpayment.due_date if there are no payments)
/*
select loan.country, to_char(nvl(lastProcessedDate, lastDueDate), 'YYYYMM') lastDate, count(*)
from loan
,(select loan_id, max(processed_date) lastProcessedDate
from payment
group by loan_id
) lastPayment
,(select loan_id, max(due_date) lastDueDate
from localPayment
group by loan_id
) lastLocalPayment
where loan.id = lastPayment.loan_id (+)
and loan.id = lastLocalPayment.loan_id (+)
and loan.status = 'defaulted'
group by loan.country, to_char(nvl(lastProcessedDate, lastDueDate), 'YYYYMM')
order by country, lastDate
;
*/
------------------------------------------------------------------------------------------------------------------------
--LOAN SIZE
------------------------------------------------------------------------------------------------------------------------
create or replace view v_loansize_comments as
select 'Smallest loan: '||smallestLoan||' Largest loan: '||largestLoan||' Average loan: '||averageLoan||' '||
'Average per borrower: $'||trim(to_char(totalAmount /
(
select count(*) numberOfBorrowers
from loan
inner join borrower on loan.id = borrower.loan_id
where loan.status not in ('refunded', 'fundraising')
), '999,990.0')) || ' '
text
from
(
select
'$'||to_char(min(loan_amount)) smallestLoan
,'$'||trim(to_char(max(loan_amount), '999,990')) largestLoan
,'$'||trim(to_char(avg(loan_amount), '999,990.0')) averageLoan
,sum(loan_amount) totalAmount
from loan
where status not in ('refunded', 'fundraising')
);
create or replace view v_loansize_fundedOverTime as
--Funded amount equal-width histogram over time
select '' html
from
(
select bucket, minLoan, maxLoan, min(yearAndQuarter) minYearAndQuarter , max(yearAndQuarter) maxYearAndQuarter
,row_number() over (order by bucket) rownumber
,to_string(cast(collect(collect_obj(trim(to_char(runningSumPercent,'990')), rownumber)) as collect_ntt), ',')
sumList
--,to_string(cast(collect(collect_obj(minLoan||' - '||maxLoan, rownumber)) as collect_ntt), '|') labels
from
(
select bucket, yearAndQuarter, minLoan, maxLoan
,sum(sumPercent) over (partition by yearAndQuarter order by bucket rows between
unbounded preceding and current row) runningSumPercent
,row_number() over (partition by bucket order by yearAndQuarter) rownumber
from
(
select bucket, yearAndQuarter, loanSum, minLoan, maxLoan
,case when sumPerYearAndQuarter = 0 then 0 else loanSum / sumPerYearAndQuarter *100 end sumPercent
from
(
select bucket, yearAndQuarter, loanSum, sum(loanSum) over (partition by yearAndQuarter) sumPerYearAndQuarter
,minLoan, maxLoan
from
(
select fundedEqualWidthHistogram.bucket, loansPerYearAndQuarter.yearAndQuarter
,sum(loansPerYearAndQuarter.loanSum) loanSum
,max(fundedEqualWidthHistogram.minLoan) minLoan
,max(fundedEqualWidthHistogram.maxLoan) maxLoan
from
(
select loans.funded_amount, quarters.yearAndQuarter, nvl(loanCount, 0) loanCount, nvl(loanSum, 0) loanSum
from
(
--Data gets interesting around this date
select distinct yearAndQuarter from mv_months where yearAndMonth >= '200602'
) quarters
left outer join
(
select funded_amount, to_char(posted_date, 'YYYYQ') yearAndQuarter, count(*) loanCount
,sum(funded_amount) loanSum
from loan
where status not in ('refunded', 'fundraising')
group by funded_amount, to_char(posted_date, 'YYYYQ')
) loans
partition by (loans.funded_amount)
on loans.yearAndQuarter = quarters.yearAndQuarter
) loansPerYearAndQuarter
,(select bucket, minLoan, maxLoan, count(*) loanCount, sum(funded_amount) loanSum
from
(
select id, funded_amount, bucket
,min(funded_amount) over (partition by bucket) minLoan
,max(funded_amount) over (partition by bucket) maxLoan
from
(
select id, funded_amount
--Add 1 to max so that the last value is included in the last bucket and not put in a separate bucket
,width_bucket(funded_amount, (select min(funded_amount) from loan)
,(select max(funded_amount)+1 from loan), 10) bucket
from loan
where status not in ('refunded', 'fundraising')
)
)
group by bucket, minLoan, maxLoan
) fundedEqualWidthHistogram
where loansPerYearAndQuarter.funded_amount >= fundedEqualWidthHistogram.minLoan
and loansPerYearAndQuarter.funded_amount <= fundedEqualWidthHistogram.maxLoan
group by bucket, loansPerYearAndQuarter.yearAndQuarter, loansPerYearAndQuarter.yearAndQuarter
) loansPerYearAndQuartAndBucket
)
)
)
group by bucket, minLoan, maxLoan
);
create or replace view v_loansize_averageLoanSize as
--Average loan size per month
--Average loan size per borrower per month
select
'' average
,'' averagePerBorrower
from
(
select
to_string(cast(collect(collect_obj(trim(to_char(average, '999,990')), rownumber)) as collect_ntt)) averageList
,to_string(cast(collect(collect_obj(trim(to_char(averagePerBorrower, '999,990')), rownumber)) as collect_ntt))
averagePerBorrowerList
,to_char(to_date(min(yearAndMonth), 'YYYYMM'), 'YYYY Mon') firstYearAndMonth
,to_char(to_date(max(yearAndMonth), 'YYYYMM'), 'YYYY Mon') lastYearAndMonth
,trim(to_char(max(average), '999,990')) largestAverage
from
(
select months.yearAndMonth, nvl(average, 0) average, nvl(averagePerBorrower, 0) averagePerBorrower
,row_number() over (order by months.yearAndMonth) rownumber
from
(
select yearAndMonth from mv_months
) months
left outer join
(
select to_char(posted_date, 'YYYYMM') yearAndMonth
,avg(funded_amount) average
,avg(funded_amount / nvl(numberOfBorrowers, 0)) averagePerBorrower
from loan
,(select loan_id, count(*) numberOfBorrowers
from borrower
group by loan_id
) borrowersPerLoan
where
loan.id = borrowersPerLoan.loan_id (+)
and loan.status not in ('refunded', 'fundraising')
group by to_char(posted_date, 'YYYYMM')
) loans
on months.yearAndMonth = loans.yearAndMonth
)
);
create or replace view v_loansize_purchasing_power as
select '' html
from
(
select trim(to_char(totalLoanAmount.totalLoanAmount / 1000000, '999'))||' million' given
,trim(to_char(totalLoanAmount * moneyMultiplier / 1000000000, '9.99'))||' billion' received
,trim(to_char(totalLoanAmount.totalLoanAmount /
(totalLoanAmount * moneyMultiplier + totalLoanAmount) * 100, '99')) givenPercent
,trim(to_char(totalLoanAmount * moneyMultiplier /
(totalLoanAmount * moneyMultiplier + totalLoanAmount) * 100, '99')) receivedPercent
from
(
--Find the average money multiplier
select avg(lenderCountry.gdpPerCapitaPPP / loanCountry.gdpPerCapitaPPP) moneyMultiplier
from loan_lender
inner join loan on loan_lender.loan_id = loan.id
inner join country loanCountry on loan.country = loanCountry .name
inner join lender on loan_lender.lender_id = lender.lender_id
inner join country lenderCountry on lender.country_code = lenderCountry.iso_code
where loan.status not in ('refunded', 'fundraising')
) averageMoneyMultiplier
cross join
(
--Total amount of money loaned
select sum(loan_amount) totalLoanAmount
from loan
where loan.status not in ('refunded', 'fundraising')
) totalLoanAmount
);
------------------------------------------------------------------------------------------------------------------------
--SECTOR/ACTIVITY
------------------------------------------------------------------------------------------------------------------------
create or replace view v_sector_loansPerActivityTop15 as
--TODO: encode %?
select '' html
from
(
select
to_string(cast(collect(collect_obj(activity||' ('||total||')', labelRowNumber)) as collect_ntt), '|') labels
,to_string(cast(collect(collect_obj(percent, dataRowNumber)) as collect_ntt), ',') data
from
(
select activity, trim(to_char(total / sum(total) over () * 100,'990')) percent
,total, row_number() over (order by total desc) dataRowNumber
,row_number() over (order by total asc) labelRowNumber
from
(
--Replace "&" with "and". (& does not work)
select replace(activity, '&', 'and') activity, count(*) total
from loan
group by activity
order by total desc
)
)
where dataRowNumber <= 15
);
create or replace view v_sector_loansPerSectorChart as
select '' html
from
(
select
to_string(cast(collect(collect_obj(sector||' ('||total||')', labelRowNumber)) as collect_ntt), '|') labels
,to_string(cast(collect(collect_obj(percent, dataRowNumber)) as collect_ntt), ',') data
from
(
select sector, trim(to_char(total / sum(total) over () * 100,'990')) percent
,total, row_number() over (order by total desc) dataRowNumber
,row_number() over (order by total asc) labelRowNumber
from
(
select sector, count(*) total
from loan
group by sector
--order by total desc
)
)
);
create or replace view v_sector_loansPerSectPerMonth as
--Loans per sector per month table with sparklines
select
--Comma does not need to be removed from last row since another group of JSON is appended to this one.
'{c:[{v:'''||sector||'''},{v:''[total]''},{v:'||grandLoanCount||'},{v:'||percentLoanCount||'}'||
',{v:'||grandLoanSum||'},{v:'||percentLoanSum||'},{v:'''||html||'''}]},' javascript
from
(
select sector, grandLoanCount, percentLoanCount, grandLoanSum, percentLoanSum
,row_number() over (order by sector) rownumber, count(*) over() numberOfRows
,(select count(distinct activity) total from loan) numberOfActivities
,'' html
from
(
select
sector, maxLoanCount, grandLoanCount, percentLoanCount, grandLoanSum, percentLoanSum
,to_string(cast(collect(collect_obj(loanCount, rownumber)) as collect_ntt), ',') data
--,f_list(cursor(select loanCount from loansPerSectorPerMonth where sector = distinctSectors.sector),',') data
from
(
select sector, months.yearAndMonth
,nvl(loanCount, 0) loanCount
,nvl(loanSum, 0) loanSum
,max(loanCount) over (partition by sector) maxLoanCount
,sum(loanCount) over (partition by sector) grandLoanCount
,trim(to_char(sum(loanCount) over (partition by sector) / sum(loanCount) over() * 100, '990.00')) percentLoanCount
,sum(loanSum) over (partition by sector) grandLoanSum
,trim(to_char(sum(loanSum) over (partition by sector) / sum(loanSum) over() * 100, '990.00')) percentLoanSum
,row_number() over (order by sector, months.yearAndMonth) rownumber
from
(
select yearAndMonth from mv_months
) months
left outer join
(
select sector, to_char(posted_date, 'YYYYMM') yearAndMonth, count(*) loanCount
,sum(funded_amount) loanSum
from loan
group by sector, to_char(posted_date, 'YYYYMM')
) loans
partition by (loans.sector)
on loans.yearAndMonth = months.yearAndMonth
order by sector, months.yearAndMonth
)
group by sector, maxLoanCount, grandLoanCount, percentLoanCount, grandLoanSum, percentLoanSum
)
)
order by sector;
create or replace view v_sector_loansPerActPerMonth as
select
'{c:[{v:'''||sector||'''},{v:'''||activity||'''},{v:'||grandLoanCount||'},{v:'||percentLoanCount||'}'||
',{v:'||grandLoanSum||'},{v:'||percentLoanSum||'},{v:'''||html||'''}]}' ||
case when rownum = numberOfRows then null else ',' end javascript
from
(
select sector, activity, grandLoanCount, percentLoanCount, grandLoanSum, percentLoanSum
,'' html
,count(*) over () numberOfRows
from
(
select
sector, activity, maxLoanCount, grandLoanCount, percentLoanCount, grandLoanSum, percentLoanSum
,to_string(cast(collect(collect_obj(loanCount, rownumber)) as collect_ntt), ',') data
from
(
select sector, activity, months.yearAndMonth
,nvl(loanCount, 0) loanCount
,nvl(loanSum, 0) loanSum
,nvl(max(loanCount) over (partition by sector, activity), 0) maxLoanCount
,nvl(sum(loanCount) over (partition by sector, activity), 0) grandLoanCount
,nvl(trim(to_char(sum(loanCount) over (partition by sector, activity) / sum(loanCount) over() * 100, '999.00')), '0') percentLoanCount
,nvl(sum(loanSum) over (partition by sector, activity), 0) grandLoanSum
,nvl(trim(to_char(sum(loanSum) over (partition by sector, activity) / sum(loanSum) over() * 100, '999.00')), '0') percentLoanSum
,row_number() over (order by sector, activity, months.yearAndMonth) rownumber
from
(
select yearAndMonth from mv_months
) months
left outer join
(
select sector, activity, to_char(posted_date, 'YYYYMM') yearAndMonth, count(*) loanCount
,sum(funded_amount) loanSum
from loan
group by sector, activity, to_char(posted_date, 'YYYYMM')
) loans
partition by (loans.sector, loans.activity)
on loans.yearAndMonth = months.yearAndMonth
order by sector, activity, months.yearAndMonth
)
group by sector, activity, maxLoanCount, grandLoanCount, percentLoanCount, grandLoanSum, percentLoanSum
)
);
create or replace view v_sector_activities as
select f_list(cursor(select distinct activity from loan order by activity), ', ') text from dual;
create or replace view v_sector_sectors as
select f_list(cursor(select distinct sector from loan order by sector), ', ') text from dual;
------------------------------------------------------------------------------------------------------------------------
--USE
------------------------------------------------------------------------------------------------------------------------
create or replace view v_use_comment as
select (select trim(to_char((select count(distinct upper(use)) from loan) /
(select count(use) from loan) * 100,'99.0'))||'%' from dual) || ' of all entries are distinct, based on a '||
'case-insensitive count, excluding periods.' text
from dual;
create or replace view v_use_top20Uses as
select '
'||use||'
'||total||'
' html
from
(
select use, total, row_number() over (order by total desc) rownumber
from
(
select upper(replace(use, '.', '')) use, count(*) total
from loan
group by upper(replace(use, '.', ''))
order by total desc
)
)
where rownumber <= 20;
create or replace view v_use_top20Words as
--29 seconds
select '
'||word||'
'||trim(to_char(frequency, '999,999'))||'
' html
from
(
select word, count(*) frequency
--21 seconds
from
(
--I know this looks stupid, but the smarter ways to do this, such as regexp_replace, cause wierd Oracle errors.
select replace(replace(replace(replace(replace(replace(upper(word),','),'-'),'—'),'.'),'('),')') word
from
(
select column_value word, rownum
from table(f_splitString(cursor(select use from loan-- where id < 1000
), ' '))
)
)
--Exclude some words
where word not in ('TO','AND','THE','OF','A','FOR','IN','DE','AS','WILL','MORE','LIKE')
group by word
order by count(*) desc
)
where rownum <= 20;
----------------------------------------------------------------------------------------------------------------------
--LANGUAGE
----------------------------------------------------------------------------------------------------------------------
create or replace view v_language_pieChart as
--Count of languages excluding English where English is one of multiple language
--(warning: large language names may get cut off)
select '' html
from
(
select
to_string(cast(collect(collect_obj(percent, rownumber)) as collect_ntt)) data
,to_string(cast(collect(collect_obj(language, rownumber)) as collect_ntt), '|') labels
from
(
select language, percent, total, row_number() over (order by total desc) rownumber
from
(
--'Spanish; Castillian' is too large for chart
select decode(language, 'Spanish; Castilian', 'Spanish', language) ||' ('||total||')' language
,trim(to_char(total / sum(total) over () * 100,'990')) percent
,total
from
(
select language.name language, count(*) total
from language
,(select loan_id, code
from
(
select loan_id, code, count(*) over (partition by loan_id)
languagesPerLoan
from loan_language
)
where languagesPerLoan = 1 or code <> 'en'
) languages
where language.code = languages.code
group by language.name
order by total desc
)
)
)
);
create or replace view v_language_percentLoansEnglish as
select
trim(to_char((select count(distinct loan_id) from loan_language where code = 'en') /
(select count(*) from loan) * 100, '990.00')) html
from dual;
------------------------------------------------------------------------------------------------------------------------
--CURRENCY
------------------------------------------------------------------------------------------------------------------------
create or replace view v_currency_table as
select
'{c:[{v:'''||currencyName||'''},{v:'''||currencyCode||'''},{v:'||grandLoanCount||'},{v:'||percentLoanCount||'}'||
',{v:'||grandLoanSum||'},{v:'||percentLoanSum||'},{v:"'||countries||'"},{v:'''||rateChart||'''}'||
',{v:'''||loanChart||'''}]}'||
--Put comma at the end of each line, except the last line
case when row_number() over (order by currencyName) = count(*) over () then null else ',' end
javascript
from
(
select currencyName, currencyCode, grandLoanCount, percentLoanCount, grandLoanSum, percentLoanSum
,f_list(cursor(
select distinct country
from loan
where disbursal_currency = currencyCode
order by country
), ', ') countries
,'' loanChart
,'' rateChart
from
(
select
currencyCode, currencyName, maxLoanCount, grandLoanCount, percentLoanCount, grandLoanSum, percentLoanSum
,to_string(cast(collect(collect_obj(loanCount, rownumber)) as collect_ntt)) loanList
--Unlike most charts, exclude months without data
,to_string(cast(collect(collect_obj(case when localPerUSD = 0 then null else localPerUSD end, rownumber))
as collect_ntt), ',', 0) rateList
,to_string(cast(collect(collect_obj(case when localPerUSD = 0 then null else localPerUSD end, rownumber))
as collect_ntt)) rateList2
,maxLocalPerUSD
from
(
select currencyCode, currencyName, months.yearAndMonth
,nvl(loanCount, 0) loanCount
,nvl(loanSum, 0) loanSum
,max(loanCount) over (partition by currencyCode) maxLoanCount
,sum(loanCount) over (partition by currencyCode) grandLoanCount
,trim(to_char(sum(loanCount) over (partition by currencyCode) / sum(loanCount) over() * 100, '990.00')) percentLoanCount
,sum(loanSum) over (partition by currencyCode) grandLoanSum
,trim(to_char(sum(loanSum) over (partition by currencyCode) / sum(loanSum) over() * 100, '990.00')) percentLoanSum
--,nvl(trim(to_char(localPerUSD, '999,999,990.000000')), '0') localPerUSD
,nvl(localPerUSD, 0) localPerUSD
,max(localPerUSD) over (partition by currencyCode) maxLocalPerUSD
,row_number() over (order by currencyCode, months.yearAndMonth) rownumber
from
(
select yearAndMonth from mv_months
) months
left outer join
(
select disbursal_currency currencyCode, currency.name currencyName, to_char(posted_date, 'YYYYMM') yearAndMonth
,count(*) loanCount
,sum(funded_amount) loanSum
,avg(disbursal_amount / loan_amount) localPerUSD
from loan, currency
where loan.disbursal_currency = currency.code
and status <> 'refunded'
--DEBUG
--where rownum <= 100
--where substr(disbursal_currency, 1, 1) = 'K'
group by disbursal_currency, currency.name, to_char(posted_date, 'YYYYMM')
) loans
partition by (loans.currencyCode, loans.currencyName)
on loans.yearAndMonth = months.yearAndMonth
order by currencyCode, months.yearAndMonth
)
group by currencyCode, currencyName, maxLoanCount, grandLoanCount, percentLoanCount, grandLoanSum, percentLoanSum
,maxLocalPerUSD
)
)
order by currencyName;
create or replace view v_currency_outliers as
--Top 20 Currency outliers
--Why are some exchange rates drastically different than others?
select --loan_id, disbursal_amount, loan_amount, code, name, disbursal_date, rate, averageRate, percentError
case
when rownum = 1 then
'
'||
'
Currency Outliers (Top 20)
'||
'
Loan ID
Local Amount
US$ Amount
Currency Name
'||
'
Currency Code
Disbursal Date
Rate
Average Rate
Percent Error
'
else null end ||
'
'||loan_id||'
'||disbursal_amount||'
'||loan_amount||'
'||code||'
'||
'
'||name||'
'||disbursal_date||'
'||rate||'
'||averageRate||'
'||
'
'||percentError||'
' ||
case when rownum = 20 then '
' else null end html
from
(
select loan_id, trim(to_char(disbursal_amount, '999,999,990.0')) disbursal_amount
,trim(to_char(loan_amount, '999,990.0')) loan_amount, name, code
,to_char(disbursal_date, 'DD Mon YYYY') disbursal_date, trim(to_char(rate, '999,990.0')) rate
,trim(to_char(averageRate, '999,990.0')) averageRate
,trim(to_char((abs(averageRate-rate) / averageRate) * 100, '999,990.0'))||'%' percentError
,(abs(averageRate-rate) / averageRate) * 100 percentErrorNumber
from
(
select id loan_id, disbursal_amount, loan_amount, currency.code, currency.name, disbursal_amount / loan_amount rate
,disbursal_date, avg(disbursal_amount / loan_amount) over (partition by disbursal_currency) averageRate
from loan, currency
where disbursal_currency <> 'USD'
and loan.status <> 'refunded'
and loan.disbursal_currency = currency.code
)
--where disbursal_currency = 'VND'
order by percentErrorNumber desc
)
where rownum <= 20;
----------------------------------------------------------------------------------------------------------------------
--BORROWER
----------------------------------------------------------------------------------------------------------------------
create or replace view v_borrower_gender as
--Gender of Borrower (%)
select
case when rownum = 1 then 'data.addRows('||max(rownumber)over()||');' else null end ||
case when gender = 'F' then
'data.setValue('||to_char(rowNumber-1)||', 0, "'||yearAndMonth||'");'||
'data.setValue('||to_char(rowNumber-1)||', 1, '||genderPercent||');'
else
'data.setValue('||to_char(rowNumber-1)||', 2, '||genderPercent||');'
end javascript
--yearAndMonth, gender, genderPercent
from
(
select to_char(to_date(yearAndMonth, 'YYYYMM'), 'Mon YYYY') yearAndMonth
,gender, trim(to_char(genderTotal / monthTotal * 100, '990.0')) genderPercent
,dense_rank() over (order by yearAndMonth) rownumber
from
(
select months.yearAndMonth, gender, nvl(total, 0) genderTotal
,sum(total) over (partition by months.yearAndMonth) monthTotal
from
(select yearAndMonth from mv_months where yearAndMonth >= '200602') months
left outer join
(select to_char(posted_date, 'YYYYMM') yearAndMonth, borrower.gender, count(*) total
from loan, borrower
where loan.status not in ('refunded', 'fundraising')
and loan.id = borrower.loan_id
group by to_char(posted_date, 'YYYYMM'), borrower.gender
) borrowerData
on months.yearAndMonth = borrowerData.yearAndMonth
order by months.yearAndMonth, gender
)
);
create or replace view v_borrower_perLoanPerMonth as
select
'' html
from
(
select to_string2(cast(collect(formattedAverage order by yearAndMonth) as varchar2_ntt),',') data
,to_char(to_date(min(yearAndMonth), 'YYYYMM'), 'YYYY Mon') firstDate
,to_char(to_date(max(yearAndMonth), 'YYYYMM'), 'YYYY Mon') lastDate
,trim(to_char(max(average), '990.0')) largestAverage
from
(
select months.yearAndMonth, average, formattedAverage
from
(select yearAndMonth from mv_months where yearAndMonth >= '200602') months
left outer join
(
select to_char(posted_date, 'YYYYMM') yearAndMonth, avg(total) average
,trim(to_char(avg(total), '990.00')) formattedAverage
from
(
select loan.id, posted_date, count(borrower.loan_id) total
from loan, borrower
where loan.id = borrower.loan_id (+)
group by loan.id, posted_date
)
group by to_char(posted_date, 'YYYYMM')
) borrowers
on months.yearAndMonth = borrowers.yearAndMonth
order by yearAndMonth
)
);
------------------------------------------------------------------------------------------------------------------------
--FUNDED DATE
------------------------------------------------------------------------------------------------------------------------
create or replace view v_fundedDate_heatmap as
select
--Start the row for hour 0
case when hour = 0 then '
'||dayname||'
' else null end ||
--For totals, sum the counts for the entire hour, else just display the the total
case when dayname = 'Totals' then '
'||sum(total) over (partition by hour)||'
'
else '
'||total||'
'
end ||
--End the row for hour 23.
case when hour = 23 then
--For the totals, the ending is the sum of everything
case when dayname = 'Totals' then '
'||sum(total) over ()||'
'
--For other rows, the ending is the sum for the day
else
'
'||sum(total) over (partition by dayname)||'
'
end
else null end html
--debug
--,day, hour, total, dayname
from
(
select daysAndHours.day, daysAndHours.hour, totals.total
,decode(daysAndHours.day, 1, 'Mon', 2, 'Tue', 3, 'Wed', 4, 'Thu', 5, 'Fri', 6, 'Sat', 7, 'Sun'
,8, 'Totals') dayName
from
(select day, hour
from
(select level day from dual connect by level <= 8) days --7 + total
cross join
(select level-1 hour from dual connect by level <= 24) hours
order by day, hour
) daysAndHours
left outer join
(
select hour, day, count(*) total
from
( --Convert all dates to EST
select
--Add 30 minutes to all times. This way the hour will be based on a 30 minute before and after range.
--For example, 4 PM is all dates from 3:30:00 to 4:29:59
to_char(convertGMTToEastern(funded_date) + 30/(24*60), 'HH24') hour
--Convert to isoDayNumber. The first day of the week is Monday, duh.
,decode(to_number(to_char(convertGMTToEastern(funded_date) + 30/(24*60), 'D'))
, 1, 7, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 7, 6) day
from loan where status not in ('refunded', 'fundraising')
--and rownum < 1000 --Speed up query for testing
)
group by hour, day
) totals
on daysAndHours.day = totals.day and daysAndHours.hour = totals.hour
)
order by day, hour;
create or replace view v_fundedDate_fastestFunded as
select '
The least time to fund a loan was '||trim(to_char(secondsToFund, '999.0'))||' seconds, for loan '||loan_id||
', '||'which was for $'||to_char(loan_amount)||'. (This excludes some early loans that were funded before '||
'they were posted.)
' html
from
(
select id loan_id, loan_amount, (funded_date - posted_date)*24*60*60 secondsToFund
from loan
where status not in ('fundraising', 'refunded') and funded_date >= posted_date
order by secondsToFund
)
where rownum = 1;
create or replace view v_fundedDate_fastestPerDollar as
select '
Adjusting for the amount of the loan, the quickest funding was for loan '||
to_char(loan_id)||', which had $'||trim(to_char(loan_amount, '999,999'))||' funded in '||
to_char(secondsToFund)||' seconds (averaging $'||trim(to_char(dollarsFundedPerSecond, '999.00'))||
' every second).
' html
from
(
select id loan_id, loan_amount, (funded_date - posted_date)*24*60*60 secondsToFund
,loan_amount / ((funded_date - posted_date)*24*60*60) dollarsFundedPerSecond
from loan
where status not in ('fundraising', 'refunded') and funded_date >= posted_date
order by dollarsFundedPerSecond desc
)
where rownum = 1;
create or replace view v_fundedDate_equalWidthHist as
select
case when rownumber = 1 then 'data.addRows('||count(bucket)over()||'); ' else null end ||
'data.setValue('||(rownumber-1)||', 0, "'||trim(to_char(minMinutesToFund,'999,990'))||' - '
||trim(to_char(maxMinutesToFund,'999,990'))||'"); '||
'data.setValue('||(rownumber-1)||', 1, '||total||');' javascript
from
(
select bucket, count(*) total, min(minutesToFund) minMinutesToFund, max(minutesToFund) maxMinutesToFund
,row_number() over (order by bucket) rownumber
from
(
select id, minutesToFund, width_bucket(minutesToFund, 0, max(minutesToFund) over (), 10) bucket
from
(
select id, posted_date, funded_date, (funded_date - posted_date)*24*60 minutesToFund
from loan
--Exclude loans with status fundraising and refunded.
--Loans without a funded_date are always in either fundraising or refunded, however there are over 900 refunded
--loans that have a funded_date, but they are still excluded from these results.
where status not in ('fundraising', 'refunded')
--Exclude 207 loans that were funded before they were posted. 205 of them were among the very first loans,
--which I think were a trial run before the website even existed. The other two, loan id 8827 and 61545, are
--several months off, and I assume they are entry errors.
and funded_date >= posted_date
)
)
group by bucket
order by bucket
);
create or replace view v_fundedDate_equalHeightHist as
--Equal-height histogram of minutes to fund
select '
'||bucket||'
'||total||'
'||minMinutesToFund||'
'||maxMinutesToFund
||'
' html
from
(
select bucket, trim(to_char(total, '999,990')) total, trim(to_char(minMinutesToFund, '999,990.0')) minMinutesToFund
,trim(to_char(maxMinutesToFund, '999,990.0')) maxMinutesToFund
from
(
select bucket, count(*) total, min(minutesToFund) minMinutesToFund, max(minutesToFund) maxMinutesToFund
from
(
select id, minutesToFund, ntile(10) over (order by minutesToFund) bucket
from
(
select id, posted_date, funded_date, (funded_date - posted_date)*24*60 minutesToFund
from loan
--Exclude loans with status fundraising and refunded.
--Loans without a funded_date are always in either fundraising or refunded, however there are over 900 refunded
--loans that have a funded_date, but they are still excluded from these results.
where status not in ('fundraising', 'refunded')
--Exclude 207 loans that were funded before they were posted. 205 of them were among the very first loans,
--which I think were a trial run before the website even existed. The other two, loan id 8827 and 61545, are
--several months off, and I assume they are entry errors.
and funded_date >= posted_date
)
)
group by bucket
order by bucket
)
);
show errors